const db = require("./db");
const response = require("./response");
const jwt = require("jsonwebtoken");
const secret = "liuhailang...";
const times = require("./times");

// 登录
async function login(username, password) {
  const sqlLang = `select * from users where username = '${username}' and password = '${password}'`;
  try {
    const data = await db.query(sqlLang);
    if (data.length) {
      // 更新token
      let token = jwt.sign({ username }, secret, { expiresIn: "10h" });
      let sql = `update users set token = '${token}' where username = '${username}'`;
      // 根据username查id和nickname
      let sqlLang = `select id, nickname from users where username = '${username}'`;

      let userData = await db.query(sqlLang); // 获取用户id和昵称
      let { id, nickname } = userData[0]; // 从查询结果中提取用户id和昵称

      db.query(sql, (err, data) => {
        if (err) throw err;
      });
      return response.success({
        id,
        username,
        nickname,
        token,
        code: 200,
      });
    } else {
      return response.fail("用户名或密码错误");
    }
  } catch (err) {
    return response.fail(err.message);
  }
}

// 注册
async function register(username, nickname, password) {
  const sqlLang = `select * from users where username = '${username}'`;
  try {
    const data = await db.query(sqlLang);
    if (data.length) {
      return response.fail("用户名已存在");
    } else {
      // 获取当前时间
      let created_at = times.getCurrentTime();
      let sql = `insert into users (username, password,created_at,nickname) values ('${username}', '${password}', '${created_at}','${nickname}')`;
      db.query(sql, (err, data) => {
        if (err) throw err;
        // 生成token
        let token = jwt.sign({ username }, secret, { expiresIn: "24h" });
        // 更新token
        let sql = `update users set token = '${token}' where username = '${username}'`;
        db.query(sql, (err, data) => {
          if (err) throw err;
        });
        return response.success({
          username,
          nickname,
          token,
        });
      });
    }
  } catch {
    return response.fail(err.message);
  }
}

// 删除注销用户，根据id删除用户，文件，文件夹
async function deleteUser(id) {
  const sqlLang = `delete from users where id = '${id}'`;
  const sqlLang1 = `delete from files where user_id = '${id}'`;
  const sqlLang2 = `delete from folders where user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    const data1 = await db.query(sqlLang1);
    const data2 = await db.query(sqlLang2);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取单个用户
async function getUser(id) {
  const sqlLang = `select * from users where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 修改用户信息
async function updateUser(id,nickname, newPassword) {
  const updateSql = `UPDATE users SET nickname = '${nickname}', password = '${newPassword}' WHERE id = '${id}'`;
  try {
    const result = await db.query(updateSql);
    if (result.affectedRows > 0) {
      return response.success("User information updated successfully");
    } else {
      return response.fail("User not found or no changes applied");
    }
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取文件列表 id
async function getFile(id) {
  const sqlLang = `select * from files where user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 搜索
async function search(keyword) {
  const sqlLang = `select * from files where filename like '%${keyword}%'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 文件上传 只要文件名、文件类型、文件大小、文件路径 用户id 上传时间
async function addFile(
  user_id,
  filename,
  size_kb,
  folder_id,
  type,
  created_at,
  relativeFilePath
) {
  const sqlLang = `insert into files (user_id, filename, size, folder_id, type, created_at,relativeFilePath) values ('${user_id}', '${filename}', '${size_kb}', '${folder_id}', '${type}', '${created_at}','${relativeFilePath}')`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 文件下载
async function downloadFile(id) {
  const sqlLang = `select * from files where id = '${id}'`;
  console.log('🗄️ 执行SQL查询:', sqlLang);
  try {
    const data = await db.query(sqlLang);
    console.log('🗄️ SQL查询结果:', {
      rowCount: data ? data.length : 0,
      firstRow: data && data.length > 0 ? data[0] : null
    });
    return response.success(data);
  } catch (err) {
    console.log('❌ SQL查询错误:', err.message);
    return response.fail(err.message);
  }
}

// 文件预览
async function previewFile(id) {
  const sqlLang = `select * from files where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 新建文件夹
async function addFolder(user_id, folder_name, created_at) {
  const sqlLang = `insert into folders (user_id,folder_name, created_at ) values ('${user_id}', '${folder_name}', '${created_at}')`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 删除文件夹
// 根据id修改recycle字段的值为0
async function deleteAsid(id) {
  const sqlLang = `update files set recycle = 1 where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 回收站
// 根据user_id查询recycle为1的数据
async function getRecycle(id) {
  const sqlLang = `select * from files where recycle = 1 and user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    // 过滤recycle为1的数据
    const filterData = data.filter((item) => {
      return item.recycle === 1;
    });
    return response.success(filterData);
  } catch (err) {
    return response.fail(err.message);
  }
}

// deleteFolder
async function deleteFolder(id) {
  const sqlLang = `delete from folders where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 恢复文件
// 根据id修改recycle字段的值为1
async function recoverFile(id) {
  const sqlLang = `update files set recycle = 0 where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 永久删除文件
async function deleteForever(id) {
  // 先根据id查询文件路径
  const sqlLang = `select relativeFilePath from files where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    let relativeFilePath = data[0].relativeFilePath;
    // 删除服务器的文件
    const fs = require("fs");
    const path = require("path");
    const filePath = path.join(__dirname, "../uploads/" + relativeFilePath);
    fs.unlink(filePath, (err) => {
      if (err) {
        console.log("没有此文件");
      }
    });
    // 删除数据库的文件
    const sqlLang1 = `delete from files where id = '${id}'`;
    const data1 = await db.query(sqlLang1);
    return response.success(data1);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 重命名文件夹
async function renameFolder(id, folder_name) {
  const sqlLang = `update folders set folder_name = '${folder_name}' where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取文件夹列表
async function getFolder(user_id) {
  // 通过user_id，查询folders表中的folder_name，created_at
  const sqlLang = `select * from folders where user_id = '${user_id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 文件分享 - 创建分享记录
async function shareFile(fileId, userId, sharePassword = null, expireHours = null, maxAccessCount = null) {
  try {
    // 生成分享码
    const shareCode = generateShareCode();
    const currentTime = times.getCurrentTime();

    // 计算过期时间
    let expireTime = null;
    if (expireHours) {
      const expireDate = new Date();
      expireDate.setHours(expireDate.getHours() + expireHours);
      expireTime = expireDate.toISOString().slice(0, 19).replace('T', ' ');
    }

    // 插入分享记录
    const insertSql = `INSERT INTO shares (file_id, user_id, share_code, share_password, expire_time, max_access_count, created_at, status)
                       VALUES ('${fileId}', '${userId}', '${shareCode}', ${sharePassword ? `'${sharePassword}'` : 'NULL'}, ${expireTime ? `'${expireTime}'` : 'NULL'}, ${maxAccessCount || 'NULL'}, '${currentTime}', 1)`;

    await db.query(insertSql);

    // 更新文件的shared状态
    const updateFileSql = `UPDATE files SET shared = 1 WHERE id = '${fileId}'`;
    await db.query(updateFileSql);

    // 获取创建的分享信息
    const selectSql = `SELECT s.*, f.filename, f.type, f.size FROM shares s
                       JOIN files f ON s.file_id = f.id
                       WHERE s.share_code = '${shareCode}'`;
    const shareData = await db.query(selectSql);

    return response.success(shareData[0]);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 生成分享码
function generateShareCode() {
  const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  let result = '';
  for (let i = 0; i < 8; i++) {
    result += chars.charAt(Math.floor(Math.random() * chars.length));
  }
  return result;
}
// 取消分享 - 通过分享ID取消
async function cancelShareFile(shareId) {
  try {
    // 获取分享信息
    const getShareSql = `SELECT file_id FROM shares WHERE id = '${shareId}'`;
    const shareInfo = await db.query(getShareSql);

    if (shareInfo.length === 0) {
      return response.fail("分享记录不存在");
    }

    const fileId = shareInfo[0].file_id;

    // 删除分享记录
    const deleteSql = `DELETE FROM shares WHERE id = '${shareId}'`;
    await db.query(deleteSql);

    // 检查该文件是否还有其他分享记录
    const checkSql = `SELECT COUNT(*) as count FROM shares WHERE file_id = '${fileId}' AND status = 1`;
    const countResult = await db.query(checkSql);

    // 如果没有其他分享记录，更新文件的shared状态
    if (countResult[0].count === 0) {
      const updateFileSql = `UPDATE files SET shared = 0 WHERE id = '${fileId}'`;
      await db.query(updateFileSql);
    }

    return response.success("取消分享成功");
  } catch (err) {
    return response.fail(err.message);
  }
}

// 通过文件ID取消所有分享
async function cancelAllSharesByFileId(fileId) {
  try {
    // 删除所有分享记录
    const deleteSql = `DELETE FROM shares WHERE file_id = '${fileId}'`;
    await db.query(deleteSql);

    // 更新文件的shared状态
    const updateFileSql = `UPDATE files SET shared = 0 WHERE id = '${fileId}'`;
    await db.query(updateFileSql);

    return response.success("取消所有分享成功");
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取用户的所有分享记录
async function getUserShares(userId) {
  try {
    const sql = `SELECT s.*, f.filename, f.type, f.size, f.relativeFilePath
                 FROM shares s
                 JOIN files f ON s.file_id = f.id
                 WHERE s.user_id = '${userId}' AND s.status = 1
                 ORDER BY s.created_at DESC`;
    const data = await db.query(sql);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 通过分享码获取分享信息
async function getShareByCode(shareCode) {
  try {
    const sql = `SELECT s.*, f.filename, f.type, f.size, f.relativeFilePath, u.username
                 FROM shares s
                 JOIN files f ON s.file_id = f.id
                 JOIN users u ON s.user_id = u.id
                 WHERE s.share_code = '${shareCode}' AND s.status = 1`;
    const data = await db.query(sql);

    if (data.length === 0) {
      return response.fail("分享不存在或已失效");
    }

    const share = data[0];

    // 检查是否过期
    if (share.expire_time && new Date(share.expire_time) < new Date()) {
      return response.fail("分享已过期");
    }

    // 检查访问次数限制
    if (share.max_access_count && share.access_count >= share.max_access_count) {
      return response.fail("分享访问次数已达上限");
    }

    return response.success(share);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 验证分享密码并增加访问次数
async function accessShare(shareCode, password = null) {
  try {
    const shareResult = await getShareByCode(shareCode);
    if (!shareResult.success) {
      return shareResult;
    }

    const share = shareResult.data;

    // 验证密码
    if (share.share_password && share.share_password !== password) {
      return response.fail("分享密码错误");
    }

    // 增加访问次数
    const updateSql = `UPDATE shares SET access_count = access_count + 1 WHERE share_code = '${shareCode}'`;
    await db.query(updateSql);

    return response.success(share);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 文件分享的作者,根据sql的files表中的user_id查询users表中的username
async function shareAuthor(id) {
  const sqlLang = `select username from users where id = (select user_id from files where id = '${id}')`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取单个文件夹下的文件列表，通过folders的id找files下面所有的folder_id的数据
async function getFolderFile(id) {
  const sqlLang = `select * from files where folder_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 多文件上传
async function addFiles(user_id, filename, size, folder_id, type, created_at) {
  const sqlLang = `insert into files (user_id, filename, size, folder_id, type, created_at) values ('${user_id}', '${filename}', '${size}', '${folder_id}', '${type}', '${created_at}')`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch {
    return response.fail(err.message);
  }
}

// 全部视频文件,根据id查询type为video/mp4的数据
async function getVideo(id) {
  const sqlLang = `select * from files where type = 'video/mp4' and user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取全部音频 只要type有audio的都是音频
async function getAudio(id) {
  const sqlLang = `select * from files where type like '%audio%' and user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取全部图片 只要type有image的都是图片
async function getImg(id) {
  const sqlLang = `select * from files where type like '%image%' and user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 获取全部文档 只要type有text的都是文档
async function getDoc(id) {
  const sqlLang = `select * from files where type like '%text%' and user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 容量管理
// 根据files表统计user_id的所有size的总数, 将size的总数根据id存到users的used_storage的字段里
async function memory(id) {
  const sqlLang = `select sum(size) as total from files where user_id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    let total = data[0].total;
    const sqlLang1 = `update users set used_storage = '${total}' where id = '${id}'`;
    // 更新used_storage
    await db.query(sqlLang1);
    // 查询used_storage
    const sqlLang2 = `select used_storage from users where id = '${id}'`;
    const data2 = await db.query(sqlLang2);
    // // 总数为500M 减去used_storage，得到剩余空间
    let surplus = 200 - data2[0].used_storage;
    // 过滤files的recycle为1的数据, // 查询files表中user_id的所有size和filename
    const sqlLang3 = `select filename,size from files where user_id = '${id}' and recycle = 0`;
    const data3 = await db.query(sqlLang3);
    return response.success({
      // 总共空间
      acount: 200,
      // 用了多少
      total,
      // 剩余空间
      surplus,
      // 文件列表
      data3,
    });
  } catch (err) {
    return response.fail(err.message);
  }
}

// 文件夹都有一个独立的id，每个文件都有一个独立的folder_id，且文件夹的id和文件的folder_id是一一对应的
// 移动文件功能，就是修改文件的folder_id
// 移动文件
async function moveFile(id, folder_id) {
  const sqlLang = `update files set folder_id = '${folder_id}' where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

// 通过id查询文件名
async function getFileName(id) {
  const sqlLang = `select filename from files where id = '${id}'`;
  try {
    const data = await db.query(sqlLang);
    return response.success(data);
  } catch (err) {
    return response.fail(err.message);
  }
}

module.exports = {
  login,
  register,
  deleteUser,
  getUser,
  getFile,
  search,
  addFile,
  downloadFile,
  deleteAsid,
  previewFile,
  addFolder,
  getFolder,
  deleteFolder,
  renameFolder,
  recoverFile,
  deleteForever,
  shareFile,
  cancelShareFile,
  cancelAllSharesByFileId,
  getUserShares,
  getShareByCode,
  accessShare,
  shareAuthor,
  getFolderFile,
  getVideo,
  addFiles,
  getAudio,
  getImg,
  getDoc,
  getRecycle,
  memory,
  moveFile,
  updateUser,
};
